Generated code - Getting started with filtering, Adapter
Preface
One of the most powerful aspects of the generated code and the framework it forms is the ability to formulate filters and
sort-clauses directly in your code and let the code evaluate them at runtime. This means that once the framework has been generated, developers
working on business logic code can formulate specific filters to request only that information necessary for the task they're currently
working on, without the requirement of a given filter in a special stored procedure.
When filters and sort clauses are used to fetch data from the persistent
storage (database), the filters and sort clauses are transformed to SQL and
embedded into the actual SQL query by the used Dynamic Query Engine and
filters are fully parameterized. This makes sure execution plans are preserved
by the database server's optimizer and at the same time the filters are not
constructed with values concatenated into the SQL query itself, so no risks for SQL injection attacks.
This section describes the low-level API on which the Linq to LLBLGen
Pro provider and the QuerySpec system are build on. It's important to know
the basics of predicate classes and how to construct them to fully utilize
the power of the full LLBLGen Pro runtime framework, even if you're using
Linq or QuerySpec in most of your queries; some functionality, like updates
based on filters, use predicates directly and require basic knowledge of the
predicate system. It's not essential to use Linq or QuerySpec to query data
using the LLBLGen Pro runtime framework, you can mix any of the three
systems at will: you can write one query in Linq, another in QuerySpec and
another using the low-level API, all will be translated to SQL using the
same pipeline: QuerySpec and Linq queries are translated to the building
blocks of the low-level API: predicates, relations, predicate expressions
and relation collections.
Adapter uses
RelationPredicateBucket objects to combine a
RelationCollection
object and a
PredicateExpression object in a single object, instead of
the SelfServicing approach of having these separated. All methods of the
DataAccessAdapter
class which accept a filter, do this by accepting a
RelationPredicateBucket
object.
Predicates and Predicate expressions
A
predicate is effectively a clause used in a WHERE statement which
will result in True or False, 'WHERE' itself is not part of the predicate.
Predicates can be grouped in a predicate expression. Predicate expressions
can also be grouped inside other predicate expressions. Predicates are
placed inside a predicate expression with the operators 'And' and 'Or'.
Predicate expressions can also be placed inside another predicate expression
with the operators 'And' and 'Or'.
Filtering is the same for entities, typed views and typed lists
as well as dynamic lists and projections of database data. To construct a predicate expression, add
Predicate and
PredicateExpression objects to the
PredicateExpression object exposed by the
RelationPredicateBucket
class.
Below is an example of a nested WHERE clause with some predicate
expressions.
... Some Select statement
WHERE
(
Table1.Foo = @param1
AND
Table1.Bar = @param2
)
OR
Table2.Bar2 = @param3
The full predicate expression used in the WHERE clause is:
(Table1.Foo = @param1 AND Table1.Bar = @param2) OR Table2.Bar2 = @param3.
The following predicates are found in this filter:
- Table1.Foo = @param1
- Table1.Bar = @param2
- Table2.Bar2 = @param3
There are 2 predicate expressions found:
- (Table1.Foo = @param1 AND Table1.Bar = @param2)
- (Table1.Foo = @param1 AND Table1.Bar = @param2) OR Table2.Bar2 = @param3
To formulate the filter correctly, we start by constructing an empty
RelationPredicateBucket instance, B, which has a new, empty
PredicateExpression
object.
Let's assume param1 has the value "One", param2 has the value "Two" and param3 has the value "Three".
// [C#]
RelationPredicateBucket B = new RelationPredicateBucket();
' [VB.NET]
Dim B As New RelationPredicateBucket()
The easiest way to proceed is then to construct predicate expression A:
// [C#]
PredicateExpression A = new PredicateExpression();
A.Add(Table1Fields.Foo == "One");
A.AddWithAnd(Table1Fields.Bar == "Two");
' [VB.NET]
Dim A As New PredicateExpression()
A.Add(Table1Fields.Foo = "One")
A.AddWithAnd(Table1Fields.Bar = "Two")
A is now constructed and we can add this predicate expression as a single predicate to the predicate expression B:
// [C#]
B.PredicateExpression.Add(A);
' [VB.NET]
B.PredicateExpression.Add(A)
There is one predicate left,
OR Table2.Bar2 = @param3. Let's add that one with the Or operator directly to B:
// [C#]
B.AddWithOr(Table2Fields.Bar2 == "Three");
' [VB.NET] .NET 2.0
B.AddWithOr(Table2Fields.Bar2 = "Three")
B now has been filled with the complete filter. To sum it up, below are the complete sections of code to construct the
complete predicate expression
// [C#]
RelationPredicateBucket B = new RelationPredicateBucket();
PredicateExpression A = new PredicateExpression();
A.Add(Table1Fields.Foo == "One");
A.AddWithAnd(Table1Fields.Bar == "Two");
B.PredicateExpression.Add(A);
B.PredicateExpression.AddWithOr(Table2Fields.Bar2 == "Three");
' [VB.NET] .NET 2.0
Dim B As New RelationPredicateBucket()
Dim A As New PredicateExpression()
A.Add(Table1Fields.Foo = "One")
A.AddWithAnd(Table1Fields.Bar = "Two")
B.PredicateExpression.Add(A)
B.PredicateExpression.AddWithOr(Table2Fields.Bar2 = "Three")
There is no maximum set for the number of predicate objects you can add to a predicate expression, nor has
a maximum been set for the number of predicate expressions you can nest into each other. As a rule of thumb, every
set of predicates that should be grouped together as a single boolean expression should be placed in a separate
PredicateExpression object: the complete contents of a
PredicateExpression
object will be placed inside a '()' pair to group the predicates
physically in the SQL query.
Creating and working with field objects
The filtering system of LLBLGen Pro uses predicate classes, which
use entity field objects (or typed view field objects) to work with. LLBLGen Pro offers a convenient way to produce entity field objects:
entitynameFields.
FieldName, and
typedviewnameFields.
FieldName. Example:
// C#
EntityField2 companyNameField = CustomerFields.CompanyName;
' VB.NET
Dim companyNameField As EntityField2 = CustomerFields.CompanyName
To utilize this feature, please add the following code to your code file:
// C#
using yourrootnamespace.HelperClasses;
' VB.NET
Imports yourrootnamespace.HelperClasses
Setting aliases, expressions and aggregates on fields
To set an aggregate function, an expression (See
Field expressions and aggregates) or an object alias,
you can use
command chaining by using special methods to set the appropriate property: using the EntityField2 methods
SetAggregateFunction(),
SetExpression() and
SetObjectAlias()
you can write all assignments in one statement.
Below an example for a filter to use in a Having clause:
// C#
// SUM(Quantity) > 4 filter
IPredicate filter = (OrderDetailsFields.Quantity.SetAggregateFunction(AggregateFunctions.Sum) > 4);
' VB.NET
' SUM(Quantity) > 4 filter
Dim filter As IPredicate = (OrderDetailsFields.Quantity.SetAggregateFunction(AggregateFunctions.Sum) > 4)